﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
using WindowsFormsApplication3;

namespace bsy
{
    /// <summary>
    /// </summary>
    public partial class Checkstand : Form
    {
        //连接字符串
        private string connStr = Properties.Settings.Default.bsyConnectionString;
        private string produceType;//商品类型
        private string produceId="";  //商品ID
        private string produceName;//商品名称
        private string producePrice;//商品价格
        private string clickTab="shangpin";//选中了哪个tab(默认为商品)
     
        public Checkstand()
        {
            InitializeComponent();
          
        }
      
        private void Checkstand_Load(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
            show_sy_dgv_produce("");//显示商品
            show_sy_dgv_server("");//显示项目
            show_sy_item_cart_dgv();//显示项目卡
            show_sy_dgv_card(""); //显示会员卡
            show_sy_register(-1); //初始化挂单列表
            init_show_sy_dgv_item();//显示项目服务信息
        }
        //订单编号生成器
        public void add_order_number(string customer_id)
        { 
           string datatime = DateTime.Now.ToString();
           datatime = datatime.Split(' ')[0];
           datatime = datatime.Replace("-", "");
           order_number.Text = datatime + customer_id;
           
        }


        //显示商品
        private void show_sy_dgv_produce(string keyword)
        {
            try
            {
            string sql = string.Format("select storeroom_id ID,number 编号,SR_name 产品名称,PS_name 分类名称,count 剩余数量,guige 规格,price 单价,remark 备注 from storeroom,productsort where storeroom.PS_id=productsort.PS_id and SR_name like '%{0}%'", keyword);
                DataSet ds = new DataSet();
                ds = CommonDB.Query(sql);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    this.sy_dgv_produce.DataSource = ds.Tables[0].DefaultView;
                    this.sy_dgv_produce.Columns[0].Visible = false;
                    this.sy_dgv_produce.Columns[2].Width = 180;
                }
                ds.Dispose();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        //显示项目
        private void show_sy_dgv_server(string keyword)
        {

            try
            {
                string sql = string.Format("select item_id ID,name 项目名,elevate 提成 from item where name like '%{0}%'",keyword);
                DataSet ds = new DataSet();
                ds = CommonDB.Query(sql);
                if (ds.Tables[0].Rows.Count > 0) {
                    this.sy_dgv_server.DataSource = ds.Tables[0].DefaultView;
                    this.sy_dgv_server.Columns[0].Visible = false;
                    this.sy_dgv_server.Columns[2].Visible = false;
                }
                ds.Dispose();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        //显示项目卡
        private void show_sy_item_cart_dgv()
        {
            try
            {
                string sql = string.Format("select cart_id ID,name 卡名,temp 次数 from cart where type='{0}' and cart_id>{1}" , "xiangmuka", 1);
                DataSet ds = new DataSet();
                ds = CommonDB.Query(sql);
                if (ds.Tables[0].Rows.Count > 0) {
                    this.sy_item_cart_dgv.DataSource = ds.Tables[0].DefaultView;
                    this.sy_item_cart_dgv.Columns[0].Visible = false;
                }
                ds.Dispose();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
           
        }

        private void init_show_sy_dgv_item()
        {
            sy_dgv_item.Columns.Add("item_cart_id", "ID");
            sy_dgv_item.Columns.Add("item_name", "项目名");
            sy_dgv_item.Columns.Add("item_elevate", "提成");
            sy_dgv_item.Columns.Add("item_price", "价格");
            show_sy_dgv_item("");
        }


        //显示项目服务的信息
        private void show_sy_dgv_item(string keyword)
        {
            try
            {
                string query1 = string.Format("select item_id,name,elevate from item where name like '%{0}%'",keyword);
                DataSet ds = new DataSet();
                ds = CommonDB.Query(query1);
                if (ds.Tables[0].Rows.Count > 0) {
                    this.sy_dgv_item.Columns[0].Visible = false;
                    this.sy_dgv_item.Columns[2].Visible = false;
                    for (int k = 0; k < ds.Tables[0].Rows.Count; k++)
                    {
                        int item_id = int.Parse(ds.Tables[0].Rows[k][0].ToString().Trim());
                        string item_name = ds.Tables[0].Rows[k][1].ToString().Trim();
                        string item_elevate = ds.Tables[0].Rows[k][2].ToString().Trim();
                        string query2 = string.Format("select Re_item_cart.Re_item_cart_id,Re_item_cart.price from Re_item_cart where Re_item_cart.item_id={0} and card_id={1}", item_id, 1);
                        string item_price = "";
                        string item_cart_id = "";
                        DataSet set = new DataSet();
                        set = CommonDB.Query(query2);
                        if (set.Tables[0].Rows.Count > 0)
                        {
                            item_cart_id = set.Tables[0].Rows[0][0].ToString().Trim();
                            item_price = set.Tables[0].Rows[0][1].ToString().Trim();
                        }
                        sy_dgv_item.Rows.Add(new object[] { item_cart_id, item_name, item_elevate, item_price });
                        set.Dispose();
                    }
                }
                ds.Dispose();
                
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        //显示会员卡的信息
        public void show_sy_dgv_card(string keyword)
        {
            try
            {
                string sql = string.Format("select cart_id ID,name 卡名,price 价格,temp 折扣 from cart where cart.type='{0}' and name like '%{1}%'", "huiyuanka", keyword);
                DataSet ds = new DataSet();
                ds = CommonDB.Query(sql);
                if (ds.Tables[0].Rows.Count > 0) {
                    this.sy_dgv_cart.DataSource = ds.Tables[0].DefaultView;
                    this.sy_dgv_cart.Columns[0].Visible = false;
                }
                ds.Dispose();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
           
        }
    
       //显示挂单信息
        public void show_sy_register(int customer_id)
        {
            try
            {
                DataSet set = new DataSet();
                string query = string.Format("select order_id 订单ID,produce_name 产品名称,price 价格,pay 实收,servertype 服务类型,produceid 产品id,discount 折扣 from orders where issettle={0} and customer_id={1} and number='{2}'", 0, customer_id, order_number.Text.ToString());
                set = CommonDB.Query(query);
               
                    this.sy_register.DataSource = set.Tables[0].DefaultView;
                    this.sy_register.Columns[0].Visible = false;
                    this.sy_register.Columns[4].Visible = false;
                    this.sy_register.Columns[5].Visible = false;
                    this.sy_register.Columns[6].Visible = false;
                    show_total();
                    set.Dispose();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
              
        }

        //统计多少钱
        public void show_total()
        {
            //统计总价
            int rows = sy_register.Rows.Count;
            if (rows < 1)
            {
                total_money.Text = "00.00元";
            }
            else
            {
                float temp = 0;
                //获取整张表的数据
                for (int i = 0; i < rows; i++)
                {
                    if (!"".Equals(sy_register.Rows[i].Cells[3].Value.ToString()))
                    {
                        temp = temp + float.Parse(sy_register.Rows[i].Cells[3].Value.ToString());
                    }
                }
                total_money.Text = temp.ToString() + "元";
            }
        }

        //下拉列表显示当前顾客卡类型信息
        private void comboBox1_Click(object sender, EventArgs e)
        {
            try
            {
                int cid = 0;
                this.comboBox1.Items.Clear();
                if (string.Empty != customer_id.Text)
                {
                    Option option = new Option("-","无");
                    this.comboBox1.Items.Add(option);

                    cid = int.Parse(customer_id.Text);
                    //查找所有没有挂失的项目卡
                    string cartType = "xiangmuka";
                    string query1 = string.Format("select Re_customer_card.card_number,item.name,cart.name from item,Re_customer_card,cart,Re_item_cart where cart.cart_id=Re_item_cart.card_id and Re_customer_card.cart_id=Re_item_cart.Re_item_cart_id and item.item_id=Re_item_cart.item_id and Re_customer_card.customer_id={0} and Re_customer_card.dossal={1} and Re_customer_card.cart_type='{2}' and cart.cart_id>1", cid, 0, cartType);
                    DataSet set1 = new DataSet();
                    set1 = CommonDB.Query(query1);
                    for (int k = 0; k < set1.Tables[0].Rows.Count; k++)
                    {
                        //下拉列表组合格式 ： 卡号--》项目名称-卡名 如 ：背部护理-半年卡
                         option = new Option(cartType + "-" + set1.Tables[0].Rows[k][0].ToString().Trim(), set1.Tables[0].Rows[k][1].ToString().Trim() +
                        "-" + set1.Tables[0].Rows[k][2].ToString().Trim());
                        this.comboBox1.Items.Add(option);
                    }
                    //查找没有挂失的会员卡
                    cartType = "huiyuanka";
                    string query2 = string.Format("select card_number,cart.name from Re_customer_card,cart where cart.cart_id=Re_customer_card.cart_id and Re_customer_card.customer_id={0} and Re_customer_card.dossal={1} and Re_customer_card.cart_type='{2}' and cart.cart_id>1", cid, 0, cartType);
                    DataSet set2 = new DataSet();
                    set2 = CommonDB.Query(query2);
                    for (int k = 0; k < set2.Tables[0].Rows.Count; k++)
                    {
                        //下拉列表组合格式：卡号--》卡名 如：金卡
                         option = new Option(cartType + "-" + set2.Tables[0].Rows[k][0].ToString().Trim(), set2.Tables[0].Rows[k][1].ToString().Trim());
                        this.comboBox1.Items.Add(option);
                    }
                    this.comboBox1.DisplayMember = "Value";
                    this.comboBox1.ValueMember = "Key";
                    set1.Dispose();
                    set2.Dispose();
                }
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
           
        }


        // 选择顾客
        private void sy_select_custormer_Click(object sender, EventArgs e)
        {
            comboBox1.Items.Clear();//清空所有选项
            comboBox1.Text = "";
            yue.Text = "0.0";
            shengyucishu.Text = "0";
            discount.Text = "1";
            sy_selectcustormer_Form form = new sy_selectcustormer_Form();
            form.ShowDialog();   //弹出选择顾客窗口
            if (form.DialogResult == DialogResult.OK)
            {
                customer_id.Text = form.CusId;//顾客ID 
                custormername.Text = form.CusName; //顾客名称
                add_order_number(form.CusId);//生成订单编号
                sy_register.DataSource = null;
                show_sy_register(int.Parse(form.CusId));
            }
        }

        //选择员工
        private void sy_select_emp_Click(object sender, EventArgs e)
        {
            sy_selecemp_Form form = new sy_selecemp_Form();
            form.ShowDialog();
            if (form.DialogResult == DialogResult.OK)
            {
                emp_id.Text = form.EmpId;//顾客ID 
                emp_name.Text = form.EmpName; //顾客名称
            }
        }

        //双击选择商品
        private void sy_dgv_produce_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
          
            produceType = "shangpin";
            if (e.RowIndex > -1)
            {
                //产品ID、名称、价格
                produceId = this.sy_dgv_produce.Rows[e.RowIndex].Cells[0].Value.ToString();
                produceName = this.sy_dgv_produce.Rows[e.RowIndex].Cells[2].Value.ToString();
                producePrice = this.sy_dgv_produce.Rows[e.RowIndex].Cells[6].Value.ToString();
                produce_name.Text = produceName;
                price.Text = producePrice; 
            }
        }

        //处理双击选择项目和项目卡的事件，从项目_卡的关系表里面查找相应的关系ID
        //产品名称处理格式： 项目名称（卡名称） 如：十二时辰背部护-半年卡
        private string serverId = "", serverName = "", servercartId = "", servercartName = "";
        private void handle_cart_Item(string id, string name, string type)
        {
            try
            {
                produceType = "xiangmuka";  //设置商品类型
                if ("server".Equals(type))
                {
                    serverId = id;
                    serverName = name;

                }
                else
                {
                    servercartId = id;
                    servercartName = name;
                }
                //组合成项目名称
                produceName = serverName + "-" + servercartName;
                produce_name.Text = produceName; //设置商品名称
                if (string.Empty != serverId && string.Empty != servercartId)
                {
                    string query = string.Format("select Re_item_cart_id,price from Re_item_cart where item_id={0} and card_id={1}", serverId, servercartId);
                    DataSet set = new DataSet();
                    set = CommonDB.Query(query);
                    if (set.Tables[0].Rows.Count > 0) //如果查找到结果
                    {
                        produceId = set.Tables[0].Rows[0][0].ToString(); //获取产品ID
                        producePrice = set.Tables[0].Rows[0][1].ToString();//获取产品价格
                        price.Text = producePrice;
                    }
                    else
                    {
                        producePrice = "";
                        price.Text = producePrice;
                    }
                    set.Dispose();
                }
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
          
        }


        //双击选择项目
        private void sy_dgv_server_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string id;
            string name;
            string serverType = "server";//“server”表示双击了项目列表
            if(e.RowIndex>-1)
            {
                id = this.sy_dgv_server.Rows[e.RowIndex].Cells[0].Value.ToString();
                name = this.sy_dgv_server.Rows[e.RowIndex].Cells[1].Value.ToString();
                handle_cart_Item(id, name, serverType); 
            }
        }

        //双击选择项目卡
        private void sy_item_cart_dgv_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string id;
            string name;
            string serverType = "itemcart"; //itemcart表示双击项目卡列表
            if (e.RowIndex > -1)
            {
                id = this.sy_item_cart_dgv.Rows[e.RowIndex].Cells[0].Value.ToString();
                name = this.sy_item_cart_dgv.Rows[e.RowIndex].Cells[1].Value.ToString();
                handle_cart_Item(id, name, serverType);
            }
        }

        //双击选择会员卡
        private void sy_dgv_cart_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            produceType = "huiyuanka"; //设置商品类型
            if (e.RowIndex > -1)
            {
                produceId   = this.sy_dgv_cart.Rows[e.RowIndex].Cells[0].Value.ToString();
                produceName = this.sy_dgv_cart.Rows[e.RowIndex].Cells[1].Value.ToString();
                producePrice = this.sy_dgv_cart.Rows[e.RowIndex].Cells[2].Value.ToString();
                produce_name.Text = produceName;
                price.Text = producePrice;
            }
        }

       
        //双击选择项目
        private void sy_dgv_item_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            produceType = "shougong";  //设置商品类型
            if (e.RowIndex > -1)
            {
                produceId = this.sy_dgv_item.Rows[e.RowIndex].Cells[0].Value.ToString();
                produceName = this.sy_dgv_item.Rows[e.RowIndex].Cells[1].Value.ToString();
               producePrice = this.sy_dgv_item.Rows[e.RowIndex].Cells[3].Value.ToString();
               price.Text = producePrice;
               produce_name.Text = produceName;
            }
        }

        //会员充值（挂单）
        private void sy_customer_recharge_Click(object sender, EventArgs e)
        {
            try
            {
                produceType = "chongzhi";
                produce_name.Text = "卡充值";
                string card_number; //卡编号
                string card_name;//卡名称
                float recharge;//金额
                float achievement=0;//业绩
                if (string.Empty == customer_id.Text)
                {
                    MessageBox.Show("请选择顾客", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                if (string.Empty == emp_id.Text)
                {
                    MessageBox.Show("请选择员工", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                if (string.Empty == comboBox1.Text)
                {
                    MessageBox.Show("请选择卡类型", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                //获取选择的项
                Option option = (Option)comboBox1.SelectedItem;
                card_number = option.Key.Split('-')[1]; //获取显示卡号
                card_name = option.Value;
                //输入充值金额
                sy_customer_recharge_Form form = new sy_customer_recharge_Form();
                form.ShowDialog();
                if (form.DialogResult == DialogResult.OK)
                {
                    recharge = float.Parse(form.Recharge);//金额
                    achievement = recharge;
                }
                else
                {
                    return;
                }
                string query = string.Format("select Re_customer_card_id from Re_customer_card where card_number='{0}'", card_number);
                if (null != CommonDB.GetSingle(query))
                {
                    produceId = CommonDB.GetSingle(query).ToString();
                }
                //更新金额
                string update = string.Format("update Re_customer_card set balance=balance+{0} where card_number='{1}'", recharge, card_number);
                string insert = string.Format("insert into orders(EMP_id,e_name,customer_id,c_name,produce_name,number,servertype,produceid,price,time,discount,elevate,issettle,pay,achievement,remark)values" +
                                                                   "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},{14},'{15}')",
                                                            emp_id.Text.ToString(), emp_name.Text.ToString(), customer_id.Text.ToString(), custormername.Text.ToString(), comboBox1.Text.ToString() + "充值", order_number.Text.ToString(),
                                                            produceType, produceId, recharge, DateTime.Now, 1, 0, 0, recharge, achievement, comboBox1.Text.ToString() + "充值了" + recharge);
                CommonDB.ExecuteSqlTran(update, insert);
                MessageBox.Show("充值成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                sy_register.DataSource = null;
                show_sy_register(int.Parse(customer_id.Text.ToString()));
                form.Close();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
          
        }

        /// <summary>
        /// 选择了卡分类后
        /// </summary>
        /// <param name="cart_type"></param>
        /// <param name="number"></param>
        private void combobox_select()
        {
            if (string.Empty == comboBox1.Text)
            {
                return;
            }
            string cart_type;
            string number;
            Option option = (Option)comboBox1.SelectedItem;
            string card_number = option.Key;
            string[] split = card_number.Split('-');
            cart_type = split[0];
            number = split[1];

            bianhao.Text = number;
            try
            {
                //剩余次数和余额
                string remains_time = "0", balance = "0";
                string query = string.Format("select balance,remains_time from Re_customer_card where card_number='{0}'", number);
                DataSet set = new DataSet();
                set = CommonDB.Query(query);
                if (set.Tables[0].Rows.Count > 0)
                {
                    balance = set.Tables[0].Rows[0][0].ToString();
                    remains_time = set.Tables[0].Rows[0][1].ToString();
                }
             
                //项目卡（不可以充值）,会员卡（可以充值）；
                if (!"".Equals(cart_type))
                {
                    if ("xiangmuka".Equals(cart_type))
                    {
                     
                        sy_customer_recharge.Enabled = false;
                        sy_customer_upgrade.Enabled = false;
                        yue.Text = "0";
                        shengyucishu.Text = remains_time;
                        discount.Text = "1.00";
                    }
                    else if ("huiyuanka".Equals(cart_type))
                    {
                      
                        sy_customer_recharge.Enabled = true;
                        sy_customer_upgrade.Enabled = true;
                         yue.Text = balance;
                        shengyucishu.Text = "0";
                        string dicount = "";
                        //获取折扣
                        string queryDicount = string.Format("select cart.temp from cart,Re_customer_card where cart.cart_id = Re_customer_card.cart_id and Re_customer_card.card_number='{0}'", number);
                        if (CommonDB.GetSingle(queryDicount) != null)
                        {
                            dicount = CommonDB.GetSingle(queryDicount).ToString();
                        }
                        discount.Text = dicount;
                    }
                }
                set.Dispose();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        
            
        }

        //下拉列表选择时发生事件
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Option option = (Option)comboBox1.SelectedItem;
            string card_number = option.Key;
            if ("-".Equals(card_number))
            {
                yue.Text = "0";
                shengyucishu.Text = "0";
                discount.Text = "1.00";
                sy_customer_recharge.Enabled = false;
                sy_customer_upgrade.Enabled = false;
            }
            combobox_select();
        }

        public string show_newCardNum()
        {
            sy_new_cartNum form = new sy_new_cartNum();
            form.ShowDialog();
            string num = "";
            if (form.DialogResult == DialogResult.OK)
            {
                num = form.CartNum;
                form.Close();
            }
            return num;
        }

        //挂单
        private void btn_register_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.Empty == customer_id.Text)
                {
                    MessageBox.Show("请选择顾客", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                if (string.Empty == emp_id.Text)
                {
                    MessageBox.Show("请选择员工", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                if ("".Equals(produceId) || "".Equals(producePrice))
                {
                    MessageBox.Show("请选择产品并输入价格", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
           
                if (!isFloat(price.Text.ToString()))
                {
                    MessageBox.Show("请输入正确的价钱格式", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                string excuse = "";
                float elevate = 0; //提成
                float achievement=0;//业绩
                float produce_discount =1; //折扣
                float produce_price = 0;//价格
                float pay = 0;//实收
                string remark = "无";

                string query;
                switch (produceType)
                {
                    //---------------------------------------商品
                    case "shangpin"://商品
                        //检验是否存有商品
                        query = string.Format("select storeroom.count from storeroom where storeroom_id ={0}", produceId);
                        int storeroomcount = int.Parse(CommonDB.GetSingle(query).ToString());
                        if (storeroomcount < 1)
                        {
                            MessageBox.Show("库存已经没有此类商品,请及时进货", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }
                        string update1 = string.Format("update storeroom set count=count-1 where storeroom_id={0}", produceId);
                        excuse = update1;
                        if (string.Empty == bianhao.Text)
                        {
                            //直接给现金
                            produce_discount = float.Parse(discount.Text.ToString()); //折扣
                            produce_price = float.Parse(price.Text.ToString());//价格
                            pay = float.Parse(price.Text.ToString());//实收
                            achievement = pay; //业绩
                            remark = "买商品:" + produce_name.Text.ToString();

                        }
                        else { 
                            //使用卡付款
                            produce_discount = float.Parse(discount.Text.ToString()); //折扣
                            produce_price = float.Parse(price.Text.ToString());//价格
                            pay = 0;//实收
                            achievement = produce_price * produce_discount / 2;//业绩为会员卡付款的一半
                            remark = "买商品:" + produce_name.Text.ToString() + "（使用会员卡）";
                            //判断是否有足够的金额
                            float zongjia = float.Parse(producePrice) * produce_discount;
                            query = string.Format("select balance from Re_customer_card where card_number='{0}'", bianhao.Text.ToString());
                            float balance = 0;
                            if (null != CommonDB.GetSingle(query))
                            {
                                balance = float.Parse(CommonDB.GetSingle(query).ToString());
                            }
                            if (balance < zongjia)
                            {
                                MessageBox.Show("余额不足,请及时充值", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                return;
                            }
                            else { 
                             //有足够的金额就扣钱
                             string update2 = string.Format("update Re_customer_card set balance = balance-{0} where card_number='{1}'", produce_discount*produce_price, bianhao.Text.ToString());
                             excuse = excuse + ";" + update2;
                            }
                        }
                       
                        break;
                    //-----------------------------------项目卡
                    case "xiangmuka":
                        //如果不是会员
                        if (custormername.Text.ToString().IndexOf("临时顾客") > -1)
                        {
                            MessageBox.Show("不能选择临时顾客，请选择会员", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }
                        string num1 = show_newCardNum(); //获取新的卡号
                        if ("".Equals(num1))  //如果为空，不做任何操作
                        {
                            return;
                        }
                       
                        //获取这张项目卡可以使用多少次
                        query = string.Format("select cart.temp from Re_item_cart,cart where Re_item_cart.card_id = cart.cart_id and Re_item_cart_id={0}", produceId);
                        string temp = CommonDB.GetSingle(query).ToString();
                         //把项目卡和用户绑定关系
                        update1 = string.Format("insert into Re_customer_card(cart_id,customer_id,card_number,balance,remains_time,cart_type,dossal)"
                                                                           + "values({0},{1},'{2}',{3},{4},'{5}',{6})",
                                                                               produceId, int.Parse(customer_id.Text), num1, 0, temp, "xiangmuka", 0);
                        
                        excuse = update1;                                                        
                       if (string.Empty == bianhao.Text)
                        {
                            //直接给现金
                            produce_discount = float.Parse(discount.Text.ToString()); //折扣
                            produce_price = float.Parse(price.Text.ToString());//价格
                            pay = float.Parse(price.Text.ToString());//实收
                            achievement = pay;//业绩
                            remark = "买项目卡:" + produce_name.Text.ToString();
                        }
                        else {
                             //使用卡付款
                            produce_discount = float.Parse(discount.Text.ToString()); //折扣
                            produce_price = float.Parse(price.Text.ToString());//价格
                            pay = 0;//实收
                            achievement = produce_discount * produce_price / 2;//业绩为会员卡付款的一半
                            remark = "买项目卡:" + produce_name.Text.ToString() + "(使用会员卡)";
                            //判断是否有足够的金额
                            float zongjia = float.Parse(producePrice) * produce_discount;
                            query = string.Format("select balance from Re_customer_card where card_number='{0}'", bianhao.Text.ToString());
                            float balance = 0;
                            if (null != CommonDB.GetSingle(query))
                            {
                                balance = float.Parse(CommonDB.GetSingle(query).ToString());
                            }
                            if (balance < zongjia)
                            {
                                MessageBox.Show("余额不足,请及时充值", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                return;
                            }
                            else { 
                             //有足够的金额就扣钱
                             string update2 = string.Format("update Re_customer_card set balance = balance-{0} where card_number='{1}'", float.Parse(producePrice), bianhao.Text.ToString());
                             excuse = excuse + ";" + update2;
                            }
                        }
                        break;
                    //-----------------------------------会员卡
                    case "huiyuanka":
                        //只有一种付款方式（直接给钱）
                        //如果是会员
                        if (custormername.Text.ToString().IndexOf("临时顾客") > -1)
                        {
                            MessageBox.Show("请选择会员", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }
                        query = string.Format("select count(*) from Re_customer_card where customer_id={0} and cart_type='{1}' and dossal={2}", customer_id.Text.ToString(), "huiyuanka", 0);
                        int count = int.Parse(CommonDB.GetSingle(query).ToString());
                        if (count >0)
                        {
                            MessageBox.Show("此会员已经拥有会员卡，不能再次购买会员卡", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }

                        string num2 = show_newCardNum();
                        if ("".Equals(num2))  //如果为空，表示不做任何操作
                        {
                            return;
                        }
                        //直接给现金
                        produce_price = float.Parse(price.Text.ToString());//价格
                        pay = float.Parse(price.Text.ToString());//实收
                        achievement = pay;
                        remark = "买" + produce_name.Text.ToString();    
                        //挂单时初始状态为挂失
                        update1 = string.Format("insert into Re_customer_card(cart_id,customer_id,card_number,balance,remains_time,cart_type,dossal) "
                                                                             + "values({0},{1},'{2}',{3},{4},'{5}',{6})",
                                                                        produceId, customer_id.Text.ToString(), num2, producePrice, 0, "huiyuanka", 0); //添加卡号
                        excuse = update1;
                        break;
                    //-----------------------------------手工服务
                    case "shougong":
                           //查看此次项目的手工提成
                           query = string.Format("select elevate from item where item_id={0}", produceId);
                           object o = CommonDB.GetSingle(query);
                            if (null != CommonDB.GetSingle(query))
                            {
                                elevate = float.Parse(CommonDB.GetSingle(query).ToString());
                            }
                        if (string.Empty == bianhao.Text)
                        {
                            //直接给现金(相当于做单次服务)
                            produce_discount = float.Parse(discount.Text.ToString()); //折扣
                            produce_price = float.Parse(price.Text.ToString()); //价格
                            pay = float.Parse(price.Text.ToString());//实收
                            achievement = pay;//业绩
                            remark = "做了一次" + produce_name.Text.ToString()+"服务";    
                        }
                        else {
                            //使用卡付款
                            produce_discount = float.Parse(discount.Text.ToString()); //折扣
                            produce_price = float.Parse(price.Text.ToString());//价格
                            pay = 0;//实收
                            //判断还有这张卡还有多少次，如果为0次，直接给现金
                            int time = 0;
                            query = string.Format("select remains_time from Re_customer_card where Re_customer_card.cart_type='{0}' and  dossal={1} and card_number='{2}'", "xiangmuka", 0, bianhao.Text.ToString());

                            o = CommonDB.GetSingle(query);
                            if (null != CommonDB.GetSingle(query))
                            {
                                temp = CommonDB.GetSingle(query).ToString();
                                time = int.Parse(temp);
                            }
                            if (time < 1)
                            {
                                if (MessageBox.Show("该项目的项目卡剩余次数以为0次，是否使用会员卡进行结账？", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
                                {
                                    //使用会员卡结账
                                    achievement = produce_discount * produce_price / 2;//业绩
                                    float zongjia = float.Parse(producePrice) * produce_discount;
                                    query = string.Format("select balance from Re_customer_card where card_number='{0}'", bianhao.Text.ToString());
                                    float balance = 0;
                                    remark = "做了一次" + produce_name.Text.ToString() + "服务(使用会员卡)";
                                    if (null != CommonDB.GetSingle(query))
                                    {
                                        balance = float.Parse(CommonDB.GetSingle(query).ToString());
                                    }
                                    if (balance < zongjia)
                                    {
                                        MessageBox.Show("余额不足,请及时充值", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                        return;
                                    }
                                    else
                                    {
                                        //扣钱
                                        update1 = string.Format("update Re_customer_card set balance = balance-{0} where card_number='{1}'", float.Parse(producePrice), bianhao.Text.ToString());
                                        excuse = update1;
                                    }
                                }
                                else {
                                    return;
                                }
                            }
                            else {
                                //使用项目卡结账
                                achievement = 0;//业绩（扣除次数）
                                string item_name = comboBox1.Text.ToString().Split('-')[0];
                                remark = "做了一次" + produce_name.Text.ToString() + "服务（使用项目卡）";    
                                if (!produce_name.Text.ToString().Equals(item_name))
                                {
                                    MessageBox.Show("该项目卡不能使用在该项目上，请校对相应的项目", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                    return;
                                }
                                update1 = string.Format("update Re_customer_card set remains_time=remains_time-1 where card_number='{0}'", bianhao.Text.ToString());
                                excuse = update1;
                            }
                        }
                        break;
                }
                string insert = string.Format("insert into orders(EMP_id,e_name,customer_id,c_name,produce_name,number,servertype,produceid,price,time,discount,elevate,issettle,pay,achievement,remark)values" +
                                                                    "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},{14},'{15}')",
                                                             emp_id.Text.ToString(), emp_name.Text.ToString(), customer_id.Text.ToString(), custormername.Text.ToString(), produce_name.Text.ToString(), order_number.Text.ToString(),
                                                             produceType, produceId, produce_price, DateTime.Now, produce_discount, elevate, 0, pay, achievement,remark);
                excuse = excuse + ";" + insert;
                if (!"".Equals(excuse))
                {
                    CommonDB.ExecuteSqlTran(excuse);
                    MessageBox.Show("挂单成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    sy_register.DataSource = null;
                    show_sy_register(int.Parse(customer_id.Text.ToString()));
                    if (produceType.Equals("shangpin")) //购买了商品刷新商品列表
                    {
                        sy_dgv_produce.DataSource = null;
                        if (string.Empty != keyword_text.Text)
                        {
                            show_sy_dgv_produce(keyword_text.Text.ToString().Trim());
                        }
                        else
                        {
                            show_sy_dgv_produce("");
                        }
                    }
                    if (string.Empty != null)
                    {
                        combobox_select(); //刷新余额和剩余次数
                    }
                }
                produceId = "";
                price.Text = "";
                produce_name.Text = "";
            }
            catch 
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        //结账(把没有结账的更新为已结账)
        private void settle_accounts_Click(object sender, EventArgs e)
        {
            try
            {
                if (sy_register.Rows.Count>0)
                {
                    string update = string.Format("update orders set issettle={0} where number='{1}' and issettle={2}", 1, order_number.Text.ToString(), 0);
                    CommonDB.ExecuteSql(update);
                    MessageBox.Show("结账成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    sy_register.DataSource = null;
                    show_sy_register(int.Parse(customer_id.Text.ToString()));
                }
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
     
        //删除选中的账单
        private void del_register_Click(object sender, EventArgs e)
        {
            try
            {
                foreach (DataGridViewRow r in sy_register.SelectedRows)
                {
                    if (!r.IsNewRow)
                    {
                        //退货处理
                        if ("shangpin".Equals(r.Cells[4].Value.ToString()))
                        {
                            string delete = string.Format("delete from orders where order_id={0}", r.Cells[0].Value.ToString());
                            float produce_price = 0;
                            //计算产品价格
                            if (float.Parse(r.Cells[3].Value.ToString()) == 0)//实收为0，表示为使用会员卡收费
                            {
                                produce_price = float.Parse(r.Cells[2].Value.ToString()) * float.Parse(r.Cells[6].Value.ToString());
                            }
                            string update1 = string.Format("update Re_customer_card set balance =balance+{0} where customer_id={1} and cart_type='{2}'", produce_price, customer_id.Text.ToString(), "huiyuanka");
                            string update2 = string.Format("update storeroom set count=count+1 where storeroom_id={0}", r.Cells[5].Value.ToString());
                            string excuse = delete + ";" + update1 + ";" + update2;
                            CommonDB.ExecuteSqlTran(excuse);
                            sy_register.Rows.Remove(r);
                            sy_dgv_produce.DataSource = null;
                            if (string.Empty != keyword_text.Text)
                            {
                                show_sy_dgv_produce(keyword_text.Text.ToString().Trim());
                            }
                            else {
                                show_sy_dgv_produce("");
                            }
                            
                            if (string.Empty != null)
                            {
                                combobox_select(); //刷新余额和剩余次数
                            }
                           
                        }
                        else
                        {
                            MessageBox.Show("不能删除挂单，只有商品挂单可以删除", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                       
                        }
                    }
                }
                show_total();
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        //卡升级
        private void sy_customer_upgrade_Click(object sender, EventArgs e)
        {
            try
            {
                produceType = "kashengji";
                produce_name.Text = "卡升级";
                string cart_id; //卡ID
                string cart_old_name;//卡名称(旧卡名);
                string cart_new_name;//卡名称(新卡名);
                string cart_old_price = "";//卡价格（旧价格）
                string cart_new_price;//卡价格（新价格）
                float recharge = 0;//差额
                string excuse;
                float achievement=0;
                if (string.Empty == comboBox1.Text)
                {
                    MessageBox.Show("请选择卡类型", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                if (string.Empty == customer_id.Text)
                {
                    MessageBox.Show("请选择员工", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                Option option = (Option)comboBox1.SelectedItem;
                cart_old_name = option.Value; //卡名（旧）

                sy_upgrede form = new sy_upgrede();
                form.ShowDialog();

                if (form.DialogResult == DialogResult.OK)
                {
                    cart_id = form.Cart_id;
                    produceId = cart_id;
                    cart_new_name = form.Cart_name;
                    cart_new_price = form.Cart_price;
                    string cart_number = bianhao.Text.ToString();
                    //查找出原来卡的价格
                    string query = string.Format("select cart.price from Re_customer_card,cart where cart.cart_id=Re_customer_card.cart_id and card_number={0}", cart_number);

                    if (null != CommonDB.GetSingle(query))
                    {
                        cart_old_price = CommonDB.GetSingle(query).ToString();
                    }
                    recharge = float.Parse(cart_new_price.ToString()) - float.Parse(cart_old_price.ToString());
                    achievement = recharge;
                    if (recharge > 0)
                    {
                        //更新顾客_卡表数据
                        string update = string.Format("update Re_customer_card set cart_id = {0},balance=balance+{1} where card_number='{2}'", cart_id, recharge, cart_number);
                        //插入订单表
                        string insert = string.Format("insert into orders(EMP_id,e_name,customer_id,c_name,produce_name,number,servertype,produceid,price,time,discount,elevate,issettle,pay,achievement,remark)values" +
                                                                       "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},{14},'{15}')",
                                                                emp_id.Text.ToString(), emp_name.Text.ToString(), customer_id.Text.ToString(), custormername.Text.ToString(), cart_old_name + "升级为" + cart_new_name, order_number.Text.ToString(),
                                                                produceType, produceId, recharge, DateTime.Now, 1, 0, 0, recharge, achievement, cart_old_name + "升级为" + cart_new_name);
                        excuse = update + ";" + insert;
                        CommonDB.ExecuteSqlTran(excuse);
                        MessageBox.Show("升级成功，" + cart_old_name + "升级为" + cart_new_name, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        sy_register.DataSource = null;
                        show_sy_register(int.Parse(customer_id.Text.ToString()));

                    }
                    else {
                        MessageBox.Show("请正确选择要升级的卡种类");
                        return;
                    }
                    
                }
            }
            catch
            {
                MessageBox.Show("数据库出错", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        //判断你是否为浮点型
        public Boolean isFloat(string f)
        {
            try
            {
                float i = float.Parse(f);
                return true;
            }
            catch
            {
                return false;
            }
        }

        private void search_Click(object sender, EventArgs e)
        {
            string keyword;
            if (string.Empty != keyword_text.Text)
            {
                keyword = keyword_text.Text.ToString().Trim();
            }else{
                keyword = "";
            
            }
                switch (clickTab)
                {
                    case "shangpin"://商品
                       show_sy_dgv_produce(keyword);//显示商品
                        break;

                    case "xiangmuka"://项目卡
                        show_sy_dgv_server(keyword);//显示项目卡
                        break;
                    case "huiyuanka"://会员卡
                        show_sy_dgv_card(keyword); //显示会员卡
                        break;
                    case "shougong"://项目（手工服务）
                        sy_dgv_item.Rows.Clear();
                        show_sy_dgv_item(keyword);//显示项目服务信息
                        break;
                  }
         
        }

        private void tabControl1_Selected(object sender, TabControlEventArgs e)
        {
            clickTab = e.TabPage.Name.ToString();
        }
    }
}